package com.ssy.lingxi.product.utils;

/**
 * @author jw.chen
 * @version 2.0.0
 * @description
 * @date 2022/3/25
 */

import com.ssy.lingxi.common.exception.BusinessException;
import com.ssy.lingxi.common.response.ResponseCode;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * excel表格工具类
 * @author jw.chen
 * @version 2.0.0
 * @date 2022/3/25
 */
public class ExcelUtil {
    /**
     * 影响最大行数
     */
    private static final int XLS_MAX_ROW = 60000;

    /**
     * 生成excel模板文档
     * @param fileName          文件名称
     * @param headerList        表格头部列名
     * @param dataMap           下拉框数据(包含级联的第一级) -- 格式：(对应列名的下标值，下拉框数据)，下标值从1开始
     * @param reactDataMap      级联数据 -- KeyIndex: 下标值从1开始; ValueIndex: 下标值从1开始
     * @param request           请求
     * @param response          响应
     */
    public static void createExcelTemplate(String fileName, List<String> headerList, Map<Integer, List<String>> dataMap, List<ExcelReactData> reactDataMap, HttpServletRequest request, HttpServletResponse response){
        //判断列头是否为空
        if(CollectionUtils.isEmpty(headerList)){
            throw new BusinessException(ResponseCode.PRODUCT_COMMODITY_IMPORT_TABLE_HEAD_NOT_NULL);
        }
        //创建表格
        HSSFWorkbook workbook = new HSSFWorkbook();
        //表头样式
        HSSFCellStyle style = getHeaderStyle(workbook);
        HSSFSheet excelSheet = workbook.createSheet("sheet");
        //生成sheet1内容
        HSSFRow rowFirst = excelSheet.createRow(0);//第一个sheet的第一行为标题
        rowFirst.setHeight((short) 800);
        //生成列头
        for(int i=0;i<headerList.size();i++){
            HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
            excelSheet.setColumnWidth(i, 6000); //设置每列的列宽
            cell.setCellStyle(style); //加样式
            cell.setCellValue(headerList.get(i)); //往单元格里写数据
        }

        //每列对应的序号
        String[] arr = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ"};

        //下拉框数据
        if(dataMap != null && dataMap.size() > 0){
            for (Map.Entry<Integer, List<String>> entry : dataMap.entrySet()) {
                int index = entry.getKey() - 1;
                List<String> valueList = entry.getValue();
                //生成下拉列表(包括级联中的第一级)
                addValidationToSheet(workbook, excelSheet, valueList.toArray(new String[0]), arr[index], index, index);
            }
        }

        //级联下拉列表
        if(reactDataMap != null && reactDataMap.size() > 0){
            reactDataMap.forEach(reactData -> addValidationToSheet(workbook, excelSheet, reactData.getDataMap(), arr[reactData.getKeyIndex() - 1], reactData.getValueIndex() - 1, reactData.getValueIndex() - 1));
        }

        //隐藏存储下拉列表数据的sheet
        hideTempDataSheet(workbook);

        try {
            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("multipart/form-data");
            //2.设置文件头：最后一个参数是设置下载文件名
            response.setHeader("Content-disposition", "attachment; filename=\"" + encodeChineseDownloadFileName(request, fileName+".xls") +"\"");
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            out.flush();
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 给sheet页，添加下拉列表
     * @param workbook    excel文件，用于添加Name
     * @param targetSheet 级联列表所在sheet页
     * @param options     级联数据 ['百度','阿里巴巴']
     * @param column      下拉列表所在列 从'A'开始
     */
    public static void addValidationToSheet(HSSFWorkbook workbook, Sheet targetSheet, Object[] options, String column, int firstCol, int lastCol) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
        String nameName = column + "_parent";

        int rowIndex = 0;
        for (Object option : options) {
            Row row = optionsSheet.createRow(rowIndex++);
            Cell cell = row.createCell(0);
            cell.setCellValue(option.toString());
        }

        createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);

        DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
        CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, firstCol, lastCol);
        targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));

    }

    /**
     * 给sheet页  添加级联下拉列表
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param options     要添加的下拉列表内容  ， keys 是下拉列表1中的内容，每个Map.Entry.Value 是对应的级联下拉列表内容
     * @param keyColumn   下拉列表1位置
     */
    public static void addValidationToSheet(HSSFWorkbook workbook, Sheet targetSheet, Map<String, List<String>> options, String keyColumn, int firstCol, int lastCol) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);

        int rowIndex = 0;
        for (Map.Entry<String, List<String>> entry : options.entrySet()) {
            String parent = formatNameName(entry.getKey());
            List<String> children = entry.getValue();

            int columnIndex = 0;
            Row row = hiddenSheet.createRow(rowIndex++);
            Cell cell;

            for (String child : children) {
                cell = row.createCell(columnIndex++);
                cell.setCellValue(child);
            }

            char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
            createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));

            DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
            CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, firstCol, lastCol);
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
        }
    }

    /**
     * 根据用户在keyColumn选择的key, 自动填充value到valueColumn
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param keyValues   匹配关系 {'百度','www.baidu.com'},{'淘宝','www.taobao.com'}
     * @param keyColumn   要匹配的列（例如 网站中文名称）
     * @param valueColumn 匹配到的内容列（例如 网址）
     * @param fromRow     下拉限制开始行
     */
    public static void addAutoMatchValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, String> keyValues, char keyColumn, char valueColumn, int fromRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);

        int rowIndex = 0;
        for (Map.Entry<String, String> kv : keyValues.entrySet()) {
            Row totalSheetRow = hiddenSheet.createRow(rowIndex++);

            Cell cell = totalSheetRow.createCell(0);
            cell.setCellValue(kv.getKey());

            cell = totalSheetRow.createCell(1);
            cell.setCellValue(kv.getValue());
        }

        for (int i = fromRow; i <= XLS_MAX_ROW; i++) {
            Row totalSheetRow = targetSheet.getRow(i);
            if (totalSheetRow == null) {
                totalSheetRow = targetSheet.createRow(i);
            }

            Cell cell = totalSheetRow.getCell((int) valueColumn - 'A');
            if (cell == null) {
                cell = totalSheetRow.createCell((int) valueColumn - 'A');
            }

            String keyCell = String.valueOf(keyColumn) + (i + 1);
            String formula = String.format("IF(ISNA(VLOOKUP(%s,%s!A:B,2,0)),\"\",VLOOKUP(%s,%s!A:B,2,0))", keyCell, hiddenSheetName, keyCell, hiddenSheetName);

            cell.setCellFormula(formula);
        }

        // init the keyColumn as comboList
//        addValidationToSheet(workbook, targetSheet, keyValues.keySet().toArray(), keyColumn, fromRow);
    }

    private static void createName(Workbook workbook, String nameName, String formula) {
        //出现特殊符号替换成下划线
        nameName = replaceSpecStr(nameName);

        Name name = workbook.getName(nameName);
        if(name == null){
            name = workbook.createName();
            name.setNameName(nameName);
            name.setRefersToFormula(formula);
        }
    }

    /**
     * 隐藏excel中的sheet页
     * @param workbook  excel
     *
     */
    private static void hideTempDataSheet(HSSFWorkbook workbook) {
        for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
            workbook.setSheetHidden(i, true);
        }
    }

    /**
     * 不可数字开头
     * @param name 单元格内容
     */
    static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }
        return name;
    }

    /**
     * 样式
     * @param wb excel
     */
    private static HSSFCellStyle getHeaderStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
		//表头样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置填充方案
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置自定义填充颜色
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        //设置单元格边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        //字体
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");//设置字体名称
        font.setFontHeightInPoints((short) 13);//字体大小
        font.setBold(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 正则替换所有特殊字符
     * @param orgStr
     * @return
     */
    public static String replaceSpecStr(String orgStr){
        if (null!=orgStr&&!"".equals(orgStr.trim())) {
            String regEx="[\\s~·`!！@#￥$%^……&*（()）\\-——\\-_=+【\\[\\]】｛{}｝\\|、\\\\；;：:‘'“”\"，,《<。.》>、/？?]";
            Pattern p = Pattern.compile(regEx);
            Matcher m = p.matcher(orgStr);
            return m.replaceAll("_");
        }
        return null;
    }

    /**
     * 文件名称中文乱码处理
     * @param request    请求
     * @param pFileName  文件名称
     */
    private static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName) throws UnsupportedEncodingException {
        String filename;
        String agent = request.getHeader("USER-AGENT");
        if (null != agent) {
            if (agent.contains("Firefox")) {//Firefox
                filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes(StandardCharsets.UTF_8)))) + "?=";
            } else if (agent.contains("Chrome")) {//Chrome
                filename = new String(pFileName.getBytes(), "ISO8859-1");
            } else {//IE7+
                filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
                filename = StringUtils.replace(filename, "+", "%20");//替换空格
            }
        } else {
            filename = pFileName;
        }
        return filename;
    }

    /**
     * 判断excel文件是否2003版
     * @param filePath 文件路径
     * @return 是否2003版
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 判断excel文件是否2007版
     * @param filePath 文件路径
     * @return 是否2007版
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 获取单元格元素
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String result;
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if (temp.equals("General")) {
                        format.applyPattern("#");
                    }
                    result = format.format(value);
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:// String类型
                result = cell.getRichStringCellValue().toString();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            default:
                result = "";
                break;
        }
        return result;
    }
}


